package com.rogers.monster.dao.impl;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.rogers.monster.dao.ProjectSearchDao;
import com.rogers.monster.util.M_Util;
import com.rogers.monster.web.project.ProjectSearchCriteria;

/**
 * @author paul.ning
 *
 */
public class ProjectSearchDaoImpl implements ProjectSearchDao {
	
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	
	public List getListOfProjects(ProjectSearchCriteria psc) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from rogers_project where 1=1 ");
		
		/*
		private String report_year;
		private String project_number;
	    private String project_name;
	    private String oracle_number;
	    private String release_month;
	    private String approved_status;
	    private String budgeted_status;
	    private String vp_name;
	    private String pm_name;
	    private String bussiness_owner;
		private Category category;*/
		
		
		if (!M_Util.isEmpty(psc.getReport_year())){
			sql.append("and year = \'").append(psc.getReport_year()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getProject_number())){
			sql.append("and project_number = \'").append(psc.getProject_number()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getProject_name())){
			sql.append("and project_name = \'").append(psc.getProject_name()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getOracle_number())){
			sql.append("and oracle_number = \'").append(psc.getOracle_number()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getRelease_month())){
			sql.append("and release_month = \'").append(psc.getRelease_month()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getApproved_status())){
			sql.append("and approve_flag = \'").append(psc.getApproved_status()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getBudgeted_status())){
			sql.append("and budgeted_flag = \'").append(psc.getBudgeted_status()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getVp_name())){
			sql.append("and vp_name = \'").append(psc.getVp_name()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getPm_name())){
			sql.append("and pm_name = \'").append(psc.getPm_name()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getBussiness_owner())){
			sql.append("and bussiness_owner = \'").append(psc.getBussiness_owner()).append("\' ");
		}
		if (!M_Util.isEmpty(psc.getCategory().getId())){
			sql.append("and category = \'").append(psc.getCategory()).append("\' ");
		}
		
		return this.jdbcTemplate.queryForList(sql.toString());
	}


	public List getListOfVPs() {
		StringBuffer sql = new StringBuffer();
		sql.append("select rg_id,rg_first_name, rg_last_name,rg_notes from reporting_group where position = 'VP' and active_status = 'Y' order by rg_first_name, rg_last_name ");
		return this.jdbcTemplate.queryForList(sql.toString());
	}
	
	
	
	public Map<String, Object> getPersonById(String id){
		String sql = "select RG_ID,RG_FIRST_NAME,RG_NOTES,RG_LAST_NAME,POSITION from reporting_group where RG_ID = ?";
		
		return this.jdbcTemplate.queryForMap(sql, new Object[]{id});
		
	}
	
	public List getCAProfilesByProjectId(String projectId) {	
		String sql ="select * from CA_PROFILE where project_id = ? ";
		List l = this.jdbcTemplate.queryForList(sql, new Object[]{projectId});
/*		if(l.size()>0){
			
			int o= 1;
		}*/
		
		return l;
	}


	public  Map<String, Object> getYearById(String id) {
		String sql = "select year from year where year_id = ?";
		
		return this.jdbcTemplate.queryForMap(sql, new Object[]{id});
		
		
	}
	
	

}
